﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Reflection;
using System.Collections;
using System.Collections.Specialized;
using System.IO;

namespace CLFramework.MSSqlExport
{
    public delegate void WriteLine(string text);

	public class ScriptingEngine
	{
        Server server;
        Scripter scripter;
        ScriptingOptions options;
        string filename;
        ScriptConfig config;

        public ScriptingEngine(Server server, ScriptConfig config)
        {
            this.options = new ScriptingOptions();
            if (server == null)
            {
                throw new ArgumentNullException("server");
            }
            if (config == null)
                throw new ArgumentNullException("config");
            this.config = config;
            this.filename = config.Filename;
            this.server = server;
            this.scripter = new Scripter(server);
            scripter.Options = options;
            options.ScriptSchema = true;
            options.Indexes = true;
            options.ClusteredIndexes = true;
            options.Default = true;
            options.NoCollation = true;
            options.IncludeHeaders = true;
            options.ExtendedProperties = true;
            options.AllowSystemObjects = false;
            options.NoCommandTerminator = false;
            options.ScriptDrops = config.ScriptDrops;
            options.SchemaQualify = config.SchemaQualify;
            options.TargetServerVersion = config.SqlServerVersion;
            options.DriForeignKeys = true;
            options.DriAllConstraints = true;
            options.SchemaQualifyForeignKeysReferences = true;
        }

        public ScriptingEngine(Server server, ScriptingOptions options, string filename)
        {
            if (options == null)
            {
                throw new ArgumentNullException("options");
            }
            this.options = options;
            if (server == null)
            {
                throw new ArgumentNullException("server");
            }
            if (filename == null)
                throw new ArgumentNullException("filename");
            this.filename = filename;
            this.server = server;
            this.scripter = new Scripter(server);
                 scripter.Options = options;
        }

        public List<Table> GetTables(Database dataBase)
        {
            return GetTables(dataBase, null);
        }

        public List<Table> GetTables(Database dataBase, string schema)
        {
            Table[] tbls = new Table[dataBase.Tables.Count];
            dataBase.Tables.CopyTo(tbls, 0);
            DependencyTree tree = scripter.DiscoverDependencies(tbls, true);
            DependencyWalker depwalker = new DependencyWalker();
            DependencyCollection depcoll = depwalker.WalkDependencies(tree);

            List<Table> tables = new List<Table>();
            foreach (DependencyCollectionNode dep in depcoll)
            {
                Table table = (Table)server.GetSmoObject(dep.Urn);
                if (schema == null || table.Schema.Equals(schema, StringComparison.CurrentCultureIgnoreCase))
                    tables.Add(table);
            }
            return tables;
        }

        public List<StoredProcedure> GetStoreProcedures(Database dataBase, string schema)
        {
            List<StoredProcedure> sps = new List<StoredProcedure>();

            foreach (StoredProcedure sp in dataBase.StoredProcedures)
            {
                if (sp.IsSystemObject)
                    continue;

                if (schema == null || sp.Schema.Equals(schema, StringComparison.CurrentCultureIgnoreCase))
                {
                    sps.Add(sp);
                    continue;
                }
            }
            return sps;
        }

        public List<View> GetViews(Database dataBase, string schema)
        {
            List<View> views = new List<View>();

            foreach (View view in dataBase.Views)
            {
                if (view.IsSystemObject)
                    continue;

                if (schema == null || view.Schema.Equals(schema, StringComparison.CurrentCultureIgnoreCase))
                {
                    views.Add(view);
                    continue;
                }
            }
            return views;
        }

        public string GetScript(Database dataBase, Schema schema)
        {
            StringBuilder script = new StringBuilder();
            WriteScript(dataBase, schema, script.WriteLine);
            return script.ToString();
        }

        public void GenerateScript(Database db, Schema schema)
        {
            using (TextWriter tw = new StreamWriter(filename, false, Encoding.Unicode))
            {
                WriteScript(db, schema, tw.WriteLine);
            }
        }

        public void GenerateData(Database db, Schema schema)
        {
            using (TextWriter tw = new StreamWriter("data-" + filename, false, Encoding.Unicode))
            {
                WriteData(db, schema, tw.WriteLine);
            }
        }

        void WriteScript(Database dataBase, Schema schema, WriteLine writeLine)
        {
            Console.WriteLine("Begin generating script");
            Console.WriteLine("generating Schema {0}", schema.Name);
            writeLine(schema.GenerateScript(options));

            List<Table> tables = GetTables(dataBase, schema.Name);
            foreach (Table table in tables)
            {
                Console.WriteLine("generating Table [{0}].[{1}]", table.Schema, table.Name);
                writeLine(table.GenerateScript(options));
            }

            foreach (View view in GetViews(dataBase, schema.Name))
            {
                Console.WriteLine("generating StoredProcedure [{0}].[{1}]", view.Schema, view.Name);
                writeLine(view.GenerateScript(options));
            }

            foreach (UserDefinedFunction func in dataBase.UserDefinedFunctions)
            {
                if (func.IsSystemObject || func.Schema != schema.Name)
                    continue;
                Console.WriteLine("generating UserDefinedFunction [{0}].[{1}]", func.Schema, func.Name);
                writeLine(func.GenerateScript(options));
            }

            foreach (StoredProcedure storedProcedure in GetStoreProcedures(dataBase, schema.Name))
            {
                Console.WriteLine("generating StoredProcedure [{0}].[{1}]", storedProcedure.Schema, storedProcedure.Name);
                writeLine(storedProcedure.GenerateScript(options));
            }

            Console.WriteLine("Generate completed!");
        }

        void WriteData(Database dataBase, Schema schema, WriteLine writeLine)
        {
            Console.WriteLine("Begin generating data");

            List<Table> tables = GetTables(dataBase, schema.Name);

            foreach (Table table in tables)
            {
                Console.WriteLine("generating Data [{0}].[{1}]", table.Schema, table.Name);

                //lance
                options.ScriptData = true;
                options.ScriptSchema = false;
                options.ScriptDataCompression = false;
                writeLine(Environment.NewLine);
                writeLine(Environment.NewLine);
                writeLine(string.Format("-- Data of [{0}].[{1}]", table.Schema, table.Name));
                table.EnumScript(options).ToList().ForEach(t => writeLine(t));
            }

        }
	}
}
